/***********************************************************************************************************************************
 * Name:	Index_Show-Statistics.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	
 */
SET IMPLICIT_TRANSACTIONS OFF;
DECLARE
	@database_id		INTEGER,
	@object_id			INTEGER,
	@index_id			INTEGER,
	@partition_number	INTEGER,
	@mode				SYSNAME;
SELECT
	@database_id		= DB_ID(),
	@object_id			= Null,
	@index_id			= Null,
	@partition_number	= Null,
	@mode				= 'SAMPLED';	-- (LIMITED | SAMPLED | DETAILED)
IF OBJECT_ID('tempdb..#stats') IS NOT Null DROP TABLE #stats;
SELECT *
  INTO #stats
  FROM sys.dm_db_index_physical_stats(@database_id, @object_id, @index_id, @partition_number, @mode);
SELECT * FROM #stats ORDER BY avg_fragmentation_in_percent DESC;
GO